import pymysql
from DBUtils.PooledDB import PooledDB


class dbmanagement(object):
    def __init__(self):
        self.host = 'localhost'
        self.port = 3306  # 端口号
        self.user = 'root'  # 用户名
        self.password = "123456mesh"  # 密码
        self.db = "MeshDB"  # 库
        self.pool = PooledDB(pymysql, mincached=1,
                         maxcached=200,
                         maxconnections=200,
                         blocking=True, host=self.host, user=self.user,
                         passwd=self.password, db=self.db, port=self.port, charset='utf8')

    def query(self, sql):
        print('sql is:',sql)
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        try:
            cursor.execute(sql)
            conn.commit()
            result = cursor.fetchall()
            print('sql result is:',result)
            return result
        finally:
            cursor.close()
            conn.close()


    def update(self, sql):
        print('sql is:', sql)
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        try:
            flag = cursor.execute(sql)
            conn.commit()
            print('sql result is:', flag)
            return flag
        finally:
            cursor.close()
            conn.close()


    def getinsertid(self, sql):
        print('sql is:', sql)
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        try:
            flag = cursor.execute(sql)
            print("成功与否",flag)
            flag2 = cursor.execute("SELECT LAST_INSERT_ID();")
            print("成功与否", flag2)
            conn.commit()
            result = cursor.fetchall()
            print("成功与否", result)
            id = result[0]['LAST_INSERT_ID()']
            print('sql--getinsertid result is:', id)
            return id
        finally:
            cursor.close()
            conn.close()
